<html>
<head>
<style type="text/css">
        body
        {
            font-family:Calibri,m Microsoft Sans Serif,Tahoma;
            font-size:14px;
            font-weight:normal;
            color:#202020;
            background-color:#C0C0C0;
        }
        div.maindiv 
        {
        	margin-left:15%;
        	padding:30px;
        	width:70%;
        	 background-color:#FEFEFE;
        }
    </style>
    </head>
<body>
<div class='maindiv' >
<pre>
Title:       INDUSTRIAL DASHBOARD
Company:     www.australtek.com
Author:      Juan Sagasti / Sebastian Endler / Nicolas Lareu
Email:       jsagasti@australtek.com
Language:    C#, javascript
Platform:    .NET, SQL Server
Technology:  WCF , AJAX, json
Level:       Advanced
Description: The code implements a WCF service that allows direct communication 
              between javascript widgets and Stored Procedures.
License:     MIT</pre>



<h1>Industrial Dashboard</h1>
<p><i>A modular SP to JSON framework based on WCF</i></p>
    
    <h2>Introduction</h2>
    <p>The framework allows the execution of stored procedures straight from javascript 
        code, and enables the use of data driven javascript widgets.</p>

<h3> Why Stored Procedures </h3>
<p>Several database software projects are based on stored procedures 
and some of them implement most of the business rules directly 
in the stored procedures themselves, thus creating a rock solid and centrally 
    managed interface 
between the user and the data. 
Implementing the business layer in SP has many advantages 
like faster execution, natural relationship with 
database objects and a very simple RPC interface. </p>

<h3> Why JSON </h3>
<p>
Usually the user interface of these systems is web-based 
and if the system is fairly modern is very common that it 
uses ajax/json or ajax/xml to retrieve the data.
We chose JSON over XML because is much lighter and faster on the client.</p>
<h3> Why javascript widgets</h3>
<p>
There are several javascript frameworks that include visual widgets 
The combination of the async nature of AJAX and the visual appeal 
of the widgets give an improved experience to the user (compared to 
the old html postback methods), almost comparable to having 
a fat client installed.</p>
<h3> Why this project</h3>

<p>Usually a database centric system is composed of three layers: the database 
    itself, a middle tier layer written in C# or Java and the User Interface. Most 
    of the time a change in any of the layers creates the need to 
    update the 3 layers, which is something we&#39;d like to avoid. With our 
    approach we eliminate the need to synchronize the middle tier.</p>
    <p>
        <img src="twothirds.png" style="height: 172px; width: 547px" /></p>

    <p>In this article we present a 
Windows Communication Foundation service 
that allows the execution of procedures 
straight from javascript code,
 enabling data driven js widgets to send/receive 
 data without having to manually code the typical C#/Java interfaces .
 </p>
    <p>We also introduce a set of interfaces&nbsp; used to develop modular widgets and 
 a couple of sample html pages with open source widgets aimed to demonstrate the characteristics of the solution.
 
 The solution is based on SQL Server 2005 + WCF + javascript but the approach is also 
        valid for other platforms as well.
</p>
    <h2>System Architecture</h2>

    <img src=Architecture.png style="height: 477px; width: 520px" /><br />
    <p>
    The stored procedures are accessed by the presentation layer either through 
    standarized widgets or directly.
    </p>
    <img alt="" src="widorgeneric.png" style="width: 477px; height: 179px" /><br />
    <p>
    If the call is through a widgetAdapter class, the procedure must be built according to the 
    widgetAdapter specification, usually requiring more than one table and 
    specific column names and types. The dataset returned is transformed in the adapter to a 
    widget json structure by the method GetResults() that all widgetsAdapters must implement. 
    
    The Chart.cs code is an example of a WidgetAdapter class
    <br />
    
    If the call does not specify widgetAdapter class, the following generic json structure is 
    returned:</p>
    <pre>
    { 
        'table1' : { 'row1': {  'column1':'value11',
                                'column2':'value12',
                                'column2':'value13',
                             },
                    'row2': {   'column1':'value21',
                                'column2':'value22',
                                'column2':'value23',
                             },
                    'row3': {   'column1':'value31',
                                'column2':'value32',
                                'column2':'value33',
                             }
                    },
                    
        'table2' : { 'row1': {  'column1':'value11',
                                'column2':'value12',
                             },
                    'row2': {   'column1':'value21',
                                'column2':'value22',
                             }
                    }
    }
    </pre>
    And in this case the data can be manually used in the javascript (see Dojo DataGrid example)
    <h3>App_Code/DALService.cs</h3>
This is the core class of the system and implements the following interface
<pre >
public interface IDALService{
    [OperationContract, WebInvoke(UriTemplate = "CallProcedure")]
    Stream CallProcedure(Stream Parameters);
      
    [OperationContract, WebInvoke(UriTemplate = "GetFile")]
    Stream GetFile(Stream Parameters);
}</pre>
<p>
The parameters of these methods are not declared explicitly in the interface because 
we wanted to deserialize them using <b>JavaScriptSerializer</b> instead of the WCF 
    standard DataContractJSONSerializer. These two methods can be called directly 
    from javascript code and they return javascript objects.

</p>
    <p>
        The first method executes a parametrized stored procedure and return the results 
        as a json-dataset or with a widget specific format.</p>
 
    <pre >
public class SQLParameter
{
    public string Name { get; set; }
    public SqlDbType Type { get; set; }
    public Object Value { get; set; }
}
public class CallProcedureParameters 
{
    public String Namespace { get; set; }
    public String Class { get; set; }
    public List<SQLParameter> SQLParameters { get; set; }
    public String Procedure { get; set; }
}
    </pre>
 <p>The CallProcedureParameter class define the parameters to be sent to 
     CallProcedure, if Namespace and Class are not sent, the DALService will return 
     the resulting dataset as a set of tables.
     </p>
    <p>The SQLParameter class has its counterpart in javascript and must be used to send 
        named parameters to the SP. 
     <h3>Scripts/IndustrialDashboard.js</h3>
<p>This file contains some classes and helper functions, it needs to be included in 
all html pages. The most important helper function is RequestJSON, this function can be changed if you decide to use jquery
or even write your own Request/JSON call in javascript</p> 

<h3>Styles/IndustrialDashboard.css</h3>
<p>Implements some basic default styles for the pages, is not really necessary 
if you are not going to use the provided widgets</p> 

<h2>Widgets Architecture</h2>
<h3>Widget Structure</h3>
<p>Each widget is installed in its own directory under the Widgets directory. 
And they all have the same structure: 
<ul>
<li>Scripts/widget.js</li> : Is the javascript code of the widget and implements the functionality.
<li>Style/widget.css</li> : Implements the style and images of the widget
<li>DLL/widget.dll</li> : A C# or VB library that implements the IWidget interface
<li>Test/widget.html</li> : A unit test for the widget
</ul>

    <p>
        Widgets are divided in two groups: filters and reports, filters are used by the 
        users to specify parameters and reports are used to display data. A typical page 
        would contain some filters and one or more reports</p>

<h2>Installing the framework</h2>
<p> In order to run this example you must have access to an IIS 6 or higher and a 
    SQL Server with a test database.</p>
    <p> You can use Visual Studio since it comes with a light version of&nbsp; IIS and a 
        light version of SQLServer called SQLExpress, just open the project by using&nbsp; 
        &quot;File-&gt;Open -&gt; Web Site&quot; and perform only steps 2 and 3.</p>
    <h3> 1 - Installing the Website</h3>
    <p> Copy the contents of the zip file to a directory, like, for example 
        &quot;C:\Inetpub\IndustrialDashboard&quot;</p>
<p>
    In IIS 6 create a website or add&nbsp; a virtual directory to an existing one 
    and point it to 
    C:\Inetpub\IndustrialDashboard and, in the virtual directory properties, select 
    &quot;Create Application&quot;</p>
    <p>
        In IIS7 create a website or add an application to an existing one and point it to 
        <a href="file:///C:/Inetpub/IndustrialDashboard">C:\Inetpub\IndustrialDashboard</a> 
        . 
        </p>
    <p>
        In some installations we encountered some permissions problems that we solved by 
        changing the Application Pool of the application</p>
<h3> 2 - Create sample data and procedures</h3>
<p>
You will also need to have a running SQL Server and execute the 
    Samples\sample.sql script on a test database called IndustrialDashboard.</p>
    <p>
        If you don&#39;t have SQL Server Management studio you might need to use the provided 
        utility osql.exe. </p>
<pre>
    osql.exe -E -S localhost\SQLEXPRESS -d IndustrialDashboard -i sample.sql 
</pre>
<p>
The default name of the test database is IndustrialDashboard and is specified in the 
first line of sample.sql, if you want to use a different database just edit that line 
    and on the next step change the connection string accordingly.
The script will create a few tables and stored procedures.
</p>

<h3> 3 - Modifying the connection string/h3>
<p>
You&#39;ll need to modify the connection string in the web.config to match the 
    database created in step 2, below is an example of how it looks like:</p>
<pre>
        &lt;connectionStrings&gt;
		&lt;add name="IndustrialDashboard" 
		connectionString="Data Source=<i>Host\ServerInstance</i>;
		Initial Catalog=<i>Database</i>;
		Integrated Security=SSPI;
		providerName="System.Data.SqlClient"/&gt;
	&lt;/connectionStrings&gt;
</pre>
<p> <b>Host :</b> Is the computer name of the server where SQL Server is installed, 
    it can be an IP address or simply "localhost" </p>
<p><b>ServerInstance:</b> If  If the SQL Server instance is named, you'll need to specify its name. 
    If you use Visual Studio the instance name would be SQLEXPRESS.</p>
<p><b>Database:</b>The nThe name of the database where your data is stored, not necessary if your stored procedures are specified with fully qualified names</p>

    
<p>&nbsp;</p>


  <h3> 4 - Give the application write permissions on Logs directory <p>
      The system writes debugging and audit information to&nbsp;logs files using the standard log4net library.
      In order to make it work the library needs to have write access to the directory where the log files 
      are stored. The location of the Logs directory can be changed in the web.config 
      if needed.</p>
      <p>
                    We need to give the application user, usually ASPNET (IIS 6) 
      or IIS_IUSRS (IIS7) permissions to this directory.&nbsp; Right click on the Logs 
          directory and look for Properties-&gt;Security to include the application user and&nbsp; 
          give it&nbsp; <i>write</i> permission.</p>
  <h3> 5 - Create Login in SQL and give execute permissions</h3>
    <p> The best method to authenticate with the database is to use Integrated Security, but this will require creating the 
proper Login (IIS_IUSRS/ASPNET) in the SQL Server and then create the user on the 
        database and assign the owner role to it. The Log files (located in the directory Logs) will help determine what 
    can be failing if the database can not be accessed.</p>
    <p>Additional Information:</p>
    <ul>
    <li>  <a href="http://msdn.microsoft.com/en-us/library/aa337562.aspx"> How to create a Login </a> </li>
    <li>  <a href="http://msdn.microsoft.com/en-us/library/aa337545.aspx"> How to create a Database User </a> </li>
    </ul>
    <h3> 5 - Checking that everything is installed OK</h3>
<p>
Open your preferred browser (IE7+, Opera, Chrome , FF) and point to the virtual directory you
just created in step 1 : http://localhost/IndustrialDashboard/default.htm 
and you should see the default.htm page that shows some of the demos installed
</p>


<h2>Points of Interest/h2>
<p>
This project is the foundation where we are building a more complete framework, 
including especially designed UI widgets, authentication and session maangement,
and the ability to export data directly to PDF and Excel 
files.
</p>
    <p>
        For more information refer to <a href=http://www.australtek.com> our company website</a>
</p>
</div>
</body>
</html>